<!DOCTYPE html>
<html lang="en">
  <head>
    <title>
        MySQL-3-SQL-优化-EXPLAIN - rulerLwx Blog
      </title>
        <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
    <meta name="viewport"
      content="width=device-width, initial-scale=1, maximum-scale=1, minimum-scale=1, user-scalable=no, minimal-ui">
    <meta name="renderer" content="webkit">
    <meta http-equiv="Cache-Control" content="no-transform" />
    <meta http-equiv="Cache-Control" content="no-siteapp" />
    <meta name="apple-mobile-web-app-capable" content="yes">
    <meta name="apple-mobile-web-app-status-bar-style" content="black">
    <meta name="format-detection" content="telephone=no,email=no,adress=no">
    
    <meta name="theme-color" content="#000000" />
    
    <meta http-equiv="window-target" content="_top" />
    
    
    <meta name="description" content="EXPLAIN 各字段介绍 字段 说明 id 编号；若编号相同，顺序从上往下执行，数据小的表先执行；若编号不同（有子查询时），编号越大越优先执行 select_type 查询类型：PRIM" />
    <meta name="generator" content="Hugo 0.73.0 with theme pure" />
    <title>MySQL-3-SQL-优化-EXPLAIN - rulerLwx Blog</title>
    
    
    <link rel="stylesheet" href="https://rulerLwx.gitee.io/css/style.min.c4bc7071f132c964c2116bca53b392933f377e5ca7b7051ed245187c621a2d3e.css">
    
    <link rel="stylesheet" href="https://cdn.staticfile.org/highlight.js/9.15.10/styles/github.min.css" async>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/tocbot/4.4.2/tocbot.css" async>
    <meta property="og:title" content="MySQL-3-SQL-优化-EXPLAIN" />
<meta property="og:description" content="EXPLAIN 各字段介绍 字段 说明 id 编号；若编号相同，顺序从上往下执行，数据小的表先执行；若编号不同（有子查询时），编号越大越优先执行 select_type 查询类型：PRIM" />
<meta property="og:type" content="article" />
<meta property="og:url" content="https://rulerLwx.gitee.io/2020/07/mysql-3-sql%E4%BC%98%E5%8C%96-explain/" />
<meta property="article:published_time" content="2020-07-01T18:58:47+08:00" />
<meta property="article:modified_time" content="2020-07-01T18:58:47+08:00" />
<meta itemprop="name" content="MySQL-3-SQL-优化-EXPLAIN">
<meta itemprop="description" content="EXPLAIN 各字段介绍 字段 说明 id 编号；若编号相同，顺序从上往下执行，数据小的表先执行；若编号不同（有子查询时），编号越大越优先执行 select_type 查询类型：PRIM">
<meta itemprop="datePublished" content="2020-07-01T18:58:47&#43;08:00" />
<meta itemprop="dateModified" content="2020-07-01T18:58:47&#43;08:00" />
<meta itemprop="wordCount" content="3017">



<meta itemprop="keywords" content="" /><meta name="twitter:card" content="summary"/>
<meta name="twitter:title" content="MySQL-3-SQL-优化-EXPLAIN"/>
<meta name="twitter:description" content="EXPLAIN 各字段介绍 字段 说明 id 编号；若编号相同，顺序从上往下执行，数据小的表先执行；若编号不同（有子查询时），编号越大越优先执行 select_type 查询类型：PRIM"/>

    <!--[if lte IE 9]>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/classlist/1.1.20170427/classList.min.js"></script>
      <![endif]-->

    <!--[if lt IE 9]>
        <script src="https://cdn.jsdelivr.net/npm/html5shiv@3.7.3/dist/html5shiv.min.js"></script>
        <script src="https://cdn.jsdelivr.net/npm/respond.js@1.4.2/dest/respond.min.js"></script>
      <![endif]-->
  </head>

  
  

  <body class="main-center theme-black" itemscope itemtype="http://schema.org/WebPage"><header class="header" itemscope itemtype="http://schema.org/WPHeader">
    <div class="slimContent">
      <div class="navbar-header">
        <div class="profile-block text-center">
          <a id="avatar" href="https://gitee.com/rulerLwx" target="_blank">
            <img class="img-circle img-rotate" src="https://rulerLwx.gitee.io/avatar.png" width="200" height="200">
          </a>
          <h2 id="name" class="hidden-xs hidden-sm">rulerLwx</h2>
          <h3 id="title" class="hidden-xs hidden-sm hidden-md">thinking...</h3>
          <small id="location" class="text-muted hidden-xs hidden-sm"><i class="icon icon-map-marker"></i>Guangzhou, China</small>
        </div><div class="search" id="search-form-wrap">
    <form class="search-form sidebar-form">
        <div class="input-group">
            <input type="text" class="search-form-input form-control" placeholder="Search" />
            <span class="input-group-btn">
                <button type="submit" class="search-form-submit btn btn-flat" onclick="return false;"><i
                        class="icon icon-search"></i></button>
            </span>
        </div>
        <div class="ins-search">
            <div class="ins-search-mask"></div>
            <div class="ins-search-container">
                <div class="ins-input-wrapper">
                    <input type="text" class="ins-search-input" placeholder="Type something..."
                        x-webkit-speech />
                    <button type="button" class="close ins-close ins-selectable" data-dismiss="modal"
                        aria-label="Close"><span aria-hidden="true">×</span></button>
                </div>
                <div class="ins-section-wrapper">
                    <div class="ins-section-container"></div>
                </div>
            </div>
        </div>
    </form>
</div>
        <button class="navbar-toggle collapsed" type="button" data-toggle="collapse" data-target="#main-navbar" aria-controls="main-navbar" aria-expanded="false">
          <span class="sr-only">Toggle navigation</span>
          <span class="icon-bar"></span>
          <span class="icon-bar"></span>
          <span class="icon-bar"></span>
        </button>
      </div>
      <nav id="main-navbar" class="collapse navbar-collapse" itemscope itemtype="http://schema.org/SiteNavigationElement" role="navigation">
        <ul class="nav navbar-nav main-nav">
            <li class="menu-item menu-item-home">
                <a href="/">
                    <i class="icon icon-home-fill"></i>
                  <span class="menu-title">Home</span>
                </a>
            </li>
            <li class="menu-item menu-item-archives">
                <a href="/posts/">
                    <i class="icon icon-archives-fill"></i>
                  <span class="menu-title">Archives</span>
                </a>
            </li>
            <li class="menu-item menu-item-categories">
                <a href="/categories/">
                    <i class="icon icon-folder"></i>
                  <span class="menu-title">Categories</span>
                </a>
            </li>
            <li class="menu-item menu-item-tags">
                <a href="/tags/">
                    <i class="icon icon-tags"></i>
                  <span class="menu-title">Tags</span>
                </a>
            </li>
            <li class="menu-item menu-item-about">
                <a href="/about/">
                    <i class="icon icon-cup-fill"></i>
                  <span class="menu-title">About</span>
                </a>
            </li>
        </ul>
      </nav>
    </div>
  </header>

<aside class="sidebar" itemscope itemtype="http://schema.org/WPSideBar">
  <div class="slimContent">
    
      <div class="widget">
    <h3 class="widget-title">Board</h3>
    <div class="widget-body">
        <div id="board">
            <div class="content">enjoy~
            </div>
        </div>
    </div>
</div>

      <div class="widget">
    <h3 class="widget-title"> Categories</h3>
    <div class="widget-body">
        <ul class="category-list">
            <li class="category-list-item"><a href="https://rulerLwx.gitee.io/categories/java-framework/" class="category-list-link">java-framework</a><span class="category-list-count">38</span></li>
            <li class="category-list-item"><a href="https://rulerLwx.gitee.io/categories/java-front-end/" class="category-list-link">java-front-end</a><span class="category-list-count">11</span></li>
            <li class="category-list-item"><a href="https://rulerLwx.gitee.io/categories/java-se/" class="category-list-link">java-se</a><span class="category-list-count">21</span></li>
            <li class="category-list-item"><a href="https://rulerLwx.gitee.io/categories/java-senior/" class="category-list-link">java-senior</a><span class="category-list-count">4</span></li>
            <li class="category-list-item"><a href="https://rulerLwx.gitee.io/categories/linux/" class="category-list-link">linux</a><span class="category-list-count">13</span></li>
            <li class="category-list-item"><a href="https://rulerLwx.gitee.io/categories/tools/" class="category-list-link">tools</a><span class="category-list-count">1</span></li>
            <li class="category-list-item"><a href="https://rulerLwx.gitee.io/categories/%E6%8A%80%E6%9C%AF%E6%9D%82%E7%83%A9/" class="category-list-link">技术杂烩</a><span class="category-list-count">4</span></li>
            <li class="category-list-item"><a href="https://rulerLwx.gitee.io/categories/%E6%95%B0%E6%8D%AE%E5%BA%93/" class="category-list-link">数据库</a><span class="category-list-count">15</span></li>
        </ul>
    </div>
</div>
      <div class="widget">
    <h3 class="widget-title"> Tags</h3>
    <div class="widget-body">
        <ul class="tag-list">
            
            
            <li class="tag-list-item"><a href="https://rulerLwx.gitee.io/tags/jvm/" class="tag-list-link">jvm</a><span
                    class="tag-list-count">1</span></li>
            
            
            <li class="tag-list-item"><a href="https://rulerLwx.gitee.io/tags/%E5%A4%9A%E7%BA%BF%E7%A8%8B/" class="tag-list-link">多线程</a><span
                    class="tag-list-count">2</span></li>
            
            
            <li class="tag-list-item"><a href="https://rulerLwx.gitee.io/tags/%E7%BD%91%E7%BB%9C%E7%BC%96%E7%A8%8B/" class="tag-list-link">网络编程</a><span
                    class="tag-list-count">3</span></li>
            
        </ul>

    </div>
</div>
      
<div class="widget">
    <h3 class="widget-title">Recent Posts</h3>
    <div class="widget-body">
        <ul class="recent-post-list list-unstyled no-thumbnail">
            <li>
                <div class="item-inner">
                    <p class="item-title">
                        <a href="https://rulerLwx.gitee.io/2020/07/%E8%87%AA%E5%B7%B1%E5%8A%A8%E6%89%8B%E4%B8%80%E6%89%B9%E9%87%8F%E7%A7%BB%E5%8A%A8%E9%87%8D%E5%91%BD%E5%90%8D%E6%96%87%E4%BB%B6/" class="title">自己动手（一）——批量移动、重命名文件</a>
                    </p>
                    <p class="item-date">
                        <time datetime="2020-07-27 17:50:02 &#43;0800 CST" itemprop="datePublished">2020-07-27</time>
                    </p>
                </div>
            </li>
            <li>
                <div class="item-inner">
                    <p class="item-title">
                        <a href="https://rulerLwx.gitee.io/2020/07/%E5%85%B3%E4%BA%8Einteger%E7%9A%84-128~127%E7%BC%93%E5%AD%98/" class="title">关于Integer的 -128~127缓存</a>
                    </p>
                    <p class="item-date">
                        <time datetime="2020-07-11 16:56:21 &#43;0800 CST" itemprop="datePublished">2020-07-11</time>
                    </p>
                </div>
            </li>
            <li>
                <div class="item-inner">
                    <p class="item-title">
                        <a href="https://rulerLwx.gitee.io/2020/07/%E8%B7%A8%E5%9F%9F%E9%97%AE%E9%A2%98/" class="title">跨域问题</a>
                    </p>
                    <p class="item-date">
                        <time datetime="2020-07-08 22:41:12 &#43;0800 CST" itemprop="datePublished">2020-07-08</time>
                    </p>
                </div>
            </li>
            <li>
                <div class="item-inner">
                    <p class="item-title">
                        <a href="https://rulerLwx.gitee.io/2020/07/%E4%B8%AA%E4%BA%BA%E5%8D%9A%E5%AE%A2%E6%90%AD%E5%BB%BA/" class="title">个人博客搭建</a>
                    </p>
                    <p class="item-date">
                        <time datetime="2020-07-05 18:58:47 &#43;0800 CST" itemprop="datePublished">2020-07-05</time>
                    </p>
                </div>
            </li>
            <li>
                <div class="item-inner">
                    <p class="item-title">
                        <a href="https://rulerLwx.gitee.io/2020/07/centos/" class="title">CentOS-</a>
                    </p>
                    <p class="item-date">
                        <time datetime="2020-07-01 18:58:47 &#43;0800 CST" itemprop="datePublished">2020-07-01</time>
                    </p>
                </div>
            </li>
        </ul>
    </div>
</div>
  </div>
</aside>

    
    
<aside class="sidebar sidebar-toc collapse" id="collapseToc" itemscope itemtype="http://schema.org/WPSideBar">
  <div class="slimContent">
    <h4 class="toc-title">Catalogue</h4>
    <nav id="toc" class="js-toc toc">

    </nav>
  </div>
</aside>
<main class="main" role="main"><div class="content">
  <article id="-" class="article article-type-" itemscope
    itemtype="http://schema.org/BlogPosting">
    
    <div class="article-header">
      <h1 itemprop="name">
  <a
    class="article-title"
    href="/2020/07/mysql-3-sql%E4%BC%98%E5%8C%96-explain/"
    >MySQL-3-SQL-优化-EXPLAIN</a
  >
</h1>

      <div class="article-meta">
        
<span class="article-date">
  <i class="icon icon-calendar-check"></i>&nbsp;
<a href="https://rulerLwx.gitee.io/2020/07/mysql-3-sql%E4%BC%98%E5%8C%96-explain/" class="article-date">
  <time datetime="2020-07-01 18:58:47 &#43;0800 CST" itemprop="datePublished">2020-07-01</time>
</a>
</span>
<span class="article-category">
  <i class="icon icon-folder"></i>&nbsp;
  <a class="article-category-link" href="/categories/%E6%95%B0%E6%8D%AE%E5%BA%93/"> 数据库 </a>
</span>

        <span class="post-comment"><i class="icon icon-comment"></i>&nbsp;<a href="/2020/07/mysql-3-sql%E4%BC%98%E5%8C%96-explain/#comments"
            class="article-comment-link">Comments</a></span>
		<span class="post-wordcount hidden-xs" itemprop="wordCount">Word Count: 3017words</span>
		<span class="post-readcount hidden-xs" itemprop="timeRequired">Read Count: 7minutes </span>
      </div>
    </div>
    <div class="article-entry marked-body js-toc-content" itemprop="articleBody">
      <h1 id="explain">EXPLAIN</h1>
<h2 id="各字段介绍">各字段介绍</h2>
<table>
<thead>
<tr>
<th>字段</th>
<th>说明</th>
</tr>
</thead>
<tbody>
<tr>
<td>id</td>
<td>编号；若编号相同，顺序从上往下执行，数据小的表先执行；若编号不同（有子查询时），编号越大越优先执行</td>
</tr>
<tr>
<td>select_type</td>
<td>查询类型：<!-- raw HTML omitted --> PRIMARY：主查询（最外层） <!-- raw HTML omitted --> SUBQUERY：子查询（非最外层）<!-- raw HTML omitted --> SIMPLE：简单查询（不包含子查询、union的查询） <!-- raw HTML omitted --> DERIVED：衍生查询（使用到了临时表）</td>
</tr>
<tr>
<td>table</td>
<td>当前查询的表</td>
</tr>
<tr>
<td>type</td>
<td>索引类型：system &gt; const &gt; eq_ref &gt; ref &gt; range &gt; index &gt; all，一般能达到  ref 或 range 就行</td>
</tr>
<tr>
<td>possible_keys</td>
<td>预测用到的索引，不准确</td>
</tr>
<tr>
<td>key</td>
<td>实际用到的索引</td>
</tr>
<tr>
<td>key_len</td>
<td>实际用到的索引长度，作用：用于判断复合索引是否被完全使用（a,b,c）</td>
</tr>
<tr>
<td>ref</td>
<td>指明当前表所参照的字段</td>
</tr>
<tr>
<td>rows</td>
<td>优化器预估的记录扫描行数</td>
</tr>
<tr>
<td>Extra</td>
<td></td>
</tr>
</tbody>
</table>
<h2 id="关于-select_type">关于 select_type</h2>
<ul>
<li>PRIMARY：主查询（最外层）</li>
<li>SUBQUERY：子查询（非最外层）</li>
<li>SIMPLE：简单查询（不包含子查询、union的查询）</li>
<li>DERIVED：衍生查询（使用到了临时表），分两种情况
<ul>
<li>a）在<code>from 子查询</code>中，子查询只有一张表</li>
<li>b）在<code>from 子查询</code>中，子查询有table1 union table2，则table1就是derived，table2是union</li>
</ul>
</li>
</ul>
<pre><code class="language-sql">select temp.name from (select * from  course where id = 1 union select * from course where id = 2) temp
</code></pre>
<h2 id="关于-type">关于 type</h2>
<ul>
<li>system（忽略）: 只有一条数据的系统表 ；或 衍生表只有一条数据的主查询</li>
<li>const：仅仅能查到一条数据的SQL ,用于Primary key 或unique索引  （类型 与索引类型有关）</li>
<li>eq_ref：唯一性索引：对于每个索引键的查询，返回匹配唯一行数据（有且只有1个，不能多 、不能0）</li>
<li>ref：非唯一性索引，对于每个索引键的查询，返回匹配的所有行（0，多）</li>
<li>range：检索指定范围的行 ,where后面是一个范围查询(between,&gt; &lt; &gt;=, 特殊：in有时候会失效，从而转为 无索引all)</li>
<li>index：查询全部索引中数据</li>
<li>all：查询全部表中的数据</li>
</ul>
<p>总结</p>
<ul>
<li>system/const: 结果只有一条数据</li>
<li>eq_ref:结果多条；但是每条数据是唯一的 ；</li>
<li>ref：结果多条；但是每条数据是是0或多条 ；</li>
</ul>
<h2 id="关于-key_len">关于 key_len</h2>
<p>key_len 表示索引长度，也可理解为字节数</p>
<ul>
<li>utf8：1个字符占3个字节</li>
<li>gbk：1个字符占2个字节</li>
<li>latin：1个字符占1个字节</li>
</ul>
<p>如果索引字段可以为Null，则会使用1个字节用于标识，+1</p>
<ul>
<li>char(20)：20*3=60 +  1(null) = 61</li>
<li>varchar(20)：20*3=60 +  1(null)  +2(用2个字节 标识可变长度)  = 63</li>
<li>int类型，长度固定为 4</li>
</ul>
<h2 id="关于-ref">关于 ref</h2>
<p>指明当前表所参照的字段</p>
<p>示例：<code>select ....where a.c = b.x ;</code>，如果b.x是常量时，ref则是const</p>
<p>注意与type中的ref值区分</p>
<h2 id="关于-rows">关于 rows</h2>
<p>实际通过索引而查询到的数据个数</p>
<h2 id="关于-extra">关于 Extra</h2>
<ul>
<li>
<p>using filesort：性能消耗大，需要“额外”的一次排序（查询）。常见于 order by 语句中（排序之前得先查询）。</p>
<ul>
<li>对于单索引， 如果排序和查找是同一个字段，则不会出现using filesort；如果排序和查找不是同一个字段，则会出现using filesort；如何避免： where哪些字段，就order by那些字段2</li>
<li>复合索引：不能跨列（最佳左前缀）；如何避免： where和order by 按照复合索引的顺序使用，不要跨列或无序使用</li>
</ul>
</li>
<li>
<p>using temporary：性能损耗大 ，用到了临时表。一般出现在group by 语句中。如何避免：查询那些列，就根据那些列 group by</p>
</li>
<li>
<p>using index：性能提升，也称为索引覆盖（覆盖索引）。</p>
<p>原因：不读取原文件，只从索引文件中获取数据（不需要回表查询），即，要查询的列全部都在索引中，就是 using index</p>
</li>
<li>
<p>using where：需要回表查询</p>
<p>什么是【回表查询】：无法从索引文件中查询到需要的列，需要到原表中查询</p>
<p>假设age是索引列，但查询语句select age,name from &hellip;where age =&hellip;,此语句中必须回原表查Name，因此会显示using where</p>
</li>
<li>
<p>impossible where：where子句永远为false</p>
<p>explain select * from test02 where a1='x&rsquo; and a1='y&rsquo;  ;</p>
</li>
</ul>
<h1 id="优化示例">优化示例</h1>
<h2 id="单表">单表</h2>
<p>💃💃💃💃💃💃💃💃💃💃💃💃💃💃</p>
<h2 id="两表">两表</h2>
<p>1）小表驱动大表</p>
<pre><code>小表：10条数据
大表：300条数据
where 小表.x = 大表.y;
</code></pre>
<p>2）索引往哪张表加？</p>
<ul>
<li>小表驱动大表</li>
<li>索引建立经常使用的字段上</li>
</ul>
<p>比如：t.cid=c.cid可知，t.cid字段使用频繁，因此给该字段加索引。</p>
<p>一般情况对于左外连接，给左表加索引；右外连接，给右表加索引</p>
<h2 id="三表">三表</h2>
<ol>
<li>小表驱动大表</li>
<li>索引建立在经常查询的字段上</li>
</ol>
<h1 id="避免索引失效的一些原则">避免索引失效的一些原则</h1>
<p>1）复合索引</p>
<ul>
<li>不要跨列或无序使用（最佳左前缀）</li>
<li>尽量使用全索引匹配（复合索引全用上）</li>
</ul>
<p>2）不要在索引上进行任何操作（计算、函数、类型转换），否则索引失效</p>
<p>对于<strong>复合索引</strong>，如果左边失效，右侧全部失效。比如复合索引 (a,b,c)，如果 b失效，则b c同时失效。</p>
<p>3）<strong>复合索引</strong>不能使用不等于（!=  &lt;&gt;）或is null (is not null)，否则自身以及右侧所有全部失效。</p>
<p>复合索引中如果有&gt;，则自身和右侧索引全部失效</p>
<p>疑问：索引一样生效 ？？？？</p>
<p><img src="https://gitee.com/leafsing/pic/raw/master/img/20200709085605.png" alt=""></p>
<p>解答：？？？</p>
<blockquote>
<p>SQL优化，是一种概率层面的优化。原因是服务层中有SQL优化器，可能会影响我们的优化。一般而言， 范围查询（&gt; &lt;  in），之后的索引失效。</p>
</blockquote>
<p>另：随机读与顺序读的优化，不是概率。可以了解下mrr</p>
<p>怎么办？使用索引覆盖（using index），即，使用全索引</p>
<p>4）like尽量以“常量”开头，不要以&rsquo;%&lsquo;开头，否则索引失效</p>
<pre><code class="language-sql">select * from xx where name like '%x%' ; --name索引失效
</code></pre>
<p>5）尽量不要使用or，否则索引失效</p>
<pre><code class="language-sql">explain select * from teacher where tname ='' or tcid &gt;1 ; --将or左侧的tname 失效
</code></pre>
<p>6）尽量不要使用类型转换（显示、隐式），否则索引失效</p>
<pre><code class="language-sql">explain select * from teacher where tname = 'abc' ;
explain select * from teacher where tname = 123 ;--程序底层将 123 -&gt; '123'，即进行了类型转换，因此索引失效
</code></pre>
<h1 id="其他的优化方法">其他的优化方法</h1>
<h2 id="exist和in">exist和in</h2>
<ul>
<li>如果主查询的数据量大，则使用In,效率高。</li>
<li>如果子查询的数据量大，则使用exist,效率高。</li>
</ul>
<h2 id="order-by-优化">order by 优化</h2>
<p>using filesort 有两种算法：双路排序、单路排序 （根据IO的次数）</p>
<p>TODO&hellip;</p>
<p>提高order by查询的策略：</p>
<ol>
<li>选择使用单路、双路 ；调整buffer的容量大小；</li>
<li>避免select * &hellip;</li>
<li>复合索引 不要跨列使用 ，避免using filesort</li>
<li>保证全部的排序字段 排序的一致性（都是升序 或 降序）</li>
</ol>
<h2 id="sql排查---慢查询日志">SQL排查 - 慢查询日志</h2>
<p>1）慢查询日志默认是关闭的；</p>
<p>2）检查是否开启了 慢查询日志：</p>
<pre><code class="language-sql">show variables like '%slow_query_log%' ;
</code></pre>
<p>3）开启慢查询日志</p>
<p>a） 临时开启</p>
<pre><code class="language-sql">set global slow_query_log = 1 ;  #在内存种开启
</code></pre>
<p>重启mysql后失效</p>
<pre><code class="language-shell">service mysql restart; #重启 mysql 服务
</code></pre>
<p>b） 永久开启</p>
<p>/etc/my.cnf 中追加配置</p>
<pre><code class="language-sql">[mysqld]
slow_query_log=1
slow_query_log_file=/var/lib/mysql/localhost-slow.log
</code></pre>
<p>备注：原my.cnf文件没有相关配置，在 [mysqld] 下追加即可；配置后重启服务。</p>
<p>4）修改慢查询阈值</p>
<p>a） 临时修改</p>
<pre><code class="language-sql">set global long_query_time = 5 ;
</code></pre>
<p>b） 永久修改</p>
<p>/etc/my.cnf 中追加配置</p>
<pre><code class="language-shell">vi /etc/my.cnf 

[mysqld]
long_query_time=3
</code></pre>
<p>5）模拟慢查询</p>
<pre><code>select sleep(4);
</code></pre>
<p>6）查看慢查询</p>
<p>方式一：</p>
<pre><code class="language-shell">cat /var/lib/mysql/localhost-slow.log
</code></pre>
<p>方式二：</p>
<h1 id="锁机制">锁机制</h1>
<p>按操作类型：</p>
<ol>
<li>读锁（共享锁）： 对同一个数据（衣服），多个读操作可以同时进行，互不干扰。</li>
<li>写锁（互斥锁）： 如果当前写操作没有完毕（买衣服的一系列操作），则无法进行其他的读操作、写操作</li>
</ol>
<p>按操作范围：</p>
<ol>
<li>表锁 ：一次性对一张表整体加锁。如MyISAM存储引擎使用表锁，开销小、加锁快；无死锁；但锁的范围大，容易发生锁冲突、并发度低。</li>
<li>行锁 ：一次性对一条数据加锁。如InnoDB存储引擎使用行锁，开销大，加锁慢；容易出现死锁；锁的范围较小，不易发生锁冲突，并发度高（很小概率 发生高并发问题：脏读、幻读、不可重复度、丢失更新等问题）。</li>
<li>页锁</li>
</ol>
<pre><code class="language-sql">SHOW OPEN TABLES;
</code></pre>
<p>TODO&hellip;</p>
<h1 id="主从同步">主从同步</h1>
<p>如何实现主从同步（原理）</p>
<p><img src="https://gitee.com/leafsing/pic/raw/master/img/20200709085652.png" alt=""></p>
<p>对上图的文字解释：</p>
<ol>
<li>master将改变的数据 记录在本地的 二进制日志中（binary log） ；该过程 称之为：二进制日志件事</li>
<li>slave将master的binary log拷贝到自己的 relay log（中继日志文件）中</li>
<li>中继日志事件，将数据读取到自己的数据库之中</li>
</ol>

    </div>
    <div class="article-footer">
<blockquote class="mt-2x">
  <ul class="post-copyright list-unstyled">
    <li class="post-copyright-link hidden-xs">
      <strong>Permalink: </strong>
      <a href="https://rulerLwx.gitee.io/2020/07/mysql-3-sql%E4%BC%98%E5%8C%96-explain/" title="MySQL-3-SQL-优化-EXPLAIN" target="_blank" rel="external">https://rulerLwx.gitee.io/2020/07/mysql-3-sql%E4%BC%98%E5%8C%96-explain/</a>
    </li>
    <li class="post-copyright-license">
      <strong>License：</strong><a href="http://creativecommons.org/licenses/by/4.0/deed.zh" target="_blank" rel="external">CC BY 4.0 CN</a>
    </li>
  </ul>
</blockquote>

<div class="panel panel-default panel-badger">
  <div class="panel-body">
    <figure class="media">
      <div class="media-left">
        <a href="https://gitee.com/rulerLwx" target="_blank" class="img-burn thumb-sm visible-lg">
          <img src="https://rulerLwx.gitee.io/avatar.png" class="img-rounded w-full" alt="">
        </a>
      </div>
      <div class="media-body">
        <h3 class="media-heading"><a href="https://gitee.com/rulerLwx" target="_blank"><span class="text-dark">rulerLwx</span><small class="ml-1x">thinking...</small></a></h3>
        <div>Good Good Study, Day Day Up~</div>
      </div>
    </figure>
  </div>
</div>
    </div>
  </article>
<section id="comments">
    <div id="vcomments"></div>
</section>

</div><nav class="bar bar-footer clearfix" data-stick-bottom>
    <div class="bar-inner">
        <ul class="pager pull-left">
            <li class="prev">
                <a href="https://rulerLwx.gitee.io/2020/07/mysql-13-qa-%E5%91%BD%E4%BB%A4-%E9%99%84%E4%BB%B6/" title="MySQL-4-QA-命令-附件"><i
                        class="icon icon-angle-left"
                        aria-hidden="true"></i><span>&nbsp;&nbsp;Older</span></a>
            </li>
            <li class="next">
                <a href="https://rulerLwx.gitee.io/2020/07/mysql-3-sql%E4%BC%98%E5%8C%96/"
                    title="MySQL-3-SQL-优化"><span>Newer&nbsp;&nbsp;</span><i
                        class="icon icon-angle-right" aria-hidden="true"></i></a>
            </li>
            
            <li class="toggle-toc">
                <a class="toggle-btn collapsed" data-toggle="collapse" href="#collapseToc" aria-expanded="false"
                    title="Catalogue" role="button">
                    <span>[&nbsp;</span><span>Catalogue</span>
                    <i class="text-collapsed icon icon-anchor"></i>
                    <i class="text-in icon icon-close"></i>
                    <span>]</span>
                </a>
            </li>
        </ul>
        <div class="bar-right">
            <div class="share-component" data-sites="weibo,qq,wechat"
                data-mobile-sites="weibo,qq,qzone"></div>
        </div>
    </div>
</nav>

</main><footer class="footer" itemscope itemtype="http://schema.org/WPFooter">
<ul class="social-links">
    <li><a href="https://gitee.com/rulerLwx" target="_blank" title="gitee" data-toggle=tooltip data-placement=top >
            <i class="icon icon-gitee"></i></a></li>
    <li><a href="https://github.com/wolf-lea" target="_blank" title="github" data-toggle=tooltip data-placement=top >
            <i class="icon icon-github"></i></a></li>
</ul>
  <div class="copyright">
    &copy;2020  -
    2020
    <div class="publishby">
        Theme by <a href="https://github.com/xiaoheiAh" target="_blank"> xiaoheiAh </a>base on<a href="https://github.com/xiaoheiAh/hugo-theme-pure" target="_blank"> pure</a>.
    </div>
  </div>
</footer>

<script src="https://cdnjs.cloudflare.com/ajax/libs/mathjax/2.7.2/MathJax.js?config=TeX-MML-AM_SVG"></script>
<script type="text/x-mathjax-config">
    MathJax.Hub.Config({
            showMathMenu: false, //disables context menu
            tex2jax: {
            inlineMath: [ ['$','$'], ['\\(','\\)'] ]
           }
    });
</script>


<script src="https://cdn.jsdelivr.net/npm/jquery@3.4.1/dist/jquery.min.js"></script>
<script>
    window.jQuery || document.write('<script src="js/jquery.min.js"><\/script>')
</script>
<script type="text/javascript" src="https://cdn.staticfile.org/highlight.js/9.15.10/highlight.min.js"></script>
<script type="text/javascript" src="https://cdn.staticfile.org/highlight.js/9.15.10/languages/python.min.js" defer></script>
<script type="text/javascript" src="https://cdn.staticfile.org/highlight.js/9.15.10/languages/javascript.min.js" defer></script><script>
    hljs.configure({
        tabReplace: '    ', 
        classPrefix: ''     
        
    })
    hljs.initHighlightingOnLoad();
</script>
<script src="https://rulerLwx.gitee.io/js/application.min.bdeb64b910570b6c41badc6a05b7afb0c8ad9efd8525de3c7257d59e786326a3.js"></script>
<script src="https://rulerLwx.gitee.io/js/plugin.min.51ff8c7317566f82259170fa36e09c4493adc9b9378b427a01ad3f017ebac7dd.js"></script>

<script>
    (function (window) {
        var INSIGHT_CONFIG = {
            TRANSLATION: {
                POSTS: 'Posts',
                PAGES: 'Pages',
                CATEGORIES: 'Categories',
                TAGS: 'Tags',
                UNTITLED: '(Untitled)',
            },
            ROOT_URL: 'https:\/\/rulerLwx.gitee.io',
            CONTENT_URL: 'https:\/\/rulerLwx.gitee.io\/searchindex.json ',
        };
        window.INSIGHT_CONFIG = INSIGHT_CONFIG;
    })(window);
</script>
<script type="text/javascript" src="https://rulerLwx.gitee.io/js/insight.min.a343cd9a5a7698336b28ef3a7c16a3a1b1d2d5fb17dc8ed04022bbe08cc5459073a15bdafa3a8a58cdd56080784bdd69fa70b1ae8597565c799c57ed00f0e120.js" defer></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/tocbot/4.4.2/tocbot.min.js"></script>
<script>
    tocbot.init({
        
        tocSelector: '.js-toc',
        
        contentSelector: '.js-toc-content',
        
        headingSelector: 'h1, h2, h3',
        
        hasInnerContainers: true,
    });
</script>

<script src="https://cdn1.lncld.net/static/js/3.0.4/av-min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/valine"></script>
<script type="text/javascript">
    var GUEST = ['nick', 'mail', 'link'];
    var meta = 'nick,mail';
    meta = meta.split(',').filter(function (item) {
        return GUEST.indexOf(item) > -1;
    });
    new Valine({
        el: '#vcomments',
        verify: null ,
        notify: null ,
        appId: 'IyAB0PSPRazTPDxitO1ddQ7O-gzGzoHsz',
        appKey: '5rBJTq4KidYF33eXwvRVhtEH',
        placeholder: 'enjoy~',
        avatar: 'mm',
        meta: meta,
        pageSize: '10' || 10,
        visitor: false 
});
</script>

  </body>
</html>
